# import jieba
import jieba
from pymysql.converters import escape_string
import pymysql

# from db import ArticleDB as LocalDB
from Redis import RedisClient


class ArticleDB():
    conn = pymysql.connect(host='10.206.16.7',
                           port=3306,
                           user='root',
                           password='Aa19950218!',
                           database='ixueshu',
                           charset='utf8',
                           autocommit=True)
    r = RedisClient()

    def reConnect(self):
        try:
            self.conn.ping()
        except:
            self.conn = pymysql.connect(host='10.206.16.7',
                           port=3306,
                           user='root',
                           password='Aa19950218!',
                           database='ixueshu',
                           charset='utf8',
                           autocommit=True)

    @staticmethod
    def generate_sql(table, data):
        data['abstract'] = ''
        cols = ", ".join('`{}`'.format(k) for k in data.keys())  # 字段名拼接
        val_cols = ', '.join('"{}"'.format(k) for k in data.values())  # 对应值的占位符拼接
        sql = """
        INSERT INTO %s(%s) VALUES(%s)
        """ % (table, cols, val_cols)
        return sql

    @staticmethod
    def generate_sql_gzlib(table, data):
        cols = ", ".join('`{}`'.format(k) for k in data.keys())  # 字段名拼接
        val_cols = ', '.join('"{}"'.format(k) for k in data.values())  # 对应值的占位符拼接
        sql = """
        INSERT INTO %s(%s) VALUES(%s)
        """ % (table, cols, val_cols)
        return sql

    def UpdateSearchKeyMaxPages(self, key, max_page):
        cursor = self.conn.cursor()
        cursor.execute(f"select * from search_key where `key`='{key}'")
        res = cursor.fetchall()
        if len(res) > 0:
            key_data = cursor._rows[0]
            if key_data[3] == -1:
                cursor.execute(f"update search_key set max_page={max_page} where `key`='{key}'")
                self.conn.commit()

    def UpdateSearchKeyGzlibMaxPages(self, key, max_page):
        cursor = self.conn.cursor()
        cursor.execute(f"select * from search_key where `key`='{key}'")
        res = cursor.fetchall()
        if len(res) > 0:
            key_data = cursor._rows[0]
            if key_data[3] == -1:
                cursor.execute(f"update search_key set max_page={max_page} where `key`='{key}'")
                self.conn.commit()

    def InsertIntoArticleDictFromGzLib(self, data):
        cursor = self.conn.cursor()
        insert_sql = ArticleDB.generate_sql('article', data)
        cursor.execute(
            f"select title,university,years from article where title='{data['title']}' and university='{data['university']}' and years='{data['years']}'")
        res = cursor.fetchall()
        if len(res) == 0:
            try:
                cursor.execute(insert_sql)
                self.conn.commit()
            except:
                with open('article_url_error_sql.log', 'a+') as f:
                    f.write(insert_sql + '\n')
        elif len(res) == 1:
            author = data['author']
            update_sql = f"update article set author='{author}' where title='{data['title']}' and university='{data['university']}' and years='{data['years']}'"
            try:
                cursor.execute(update_sql)
                self.conn.commit()
            except:
                with open('article_url_error_sql.log', 'a+') as f:
                    f.write(update_sql + '\n')
        else:
            with open('article_url_error_sql.log', 'a+') as f:
                f.write(data['title'] + data['university'] + data['years'] + '\n')

    def InsertIntoArticleDict(self, data):
        cursor = self.conn.cursor()
        insert_sql = ArticleDB.generate_sql('article', data)
        cursor.execute(
            f"select title,university,years,article_id,abstract from article where title='{data['title']}' and university='{data['university']}' and years='{data['years']}'")
        res = cursor.fetchall()
        if len(res) == 0:
            try:
                cursor.execute(insert_sql)
                self.conn.commit()
            except:
                with open('article_url_error_sql.log', 'a+') as f:
                    f.write(insert_sql + '\n')
        elif len(res) == 1:
            article_id = data['article_id']
            update_sql = f"update article set article_id='{article_id}' where title='{data['title']}' and university='{data['university']}' and years='{data['years']}'"
            try:
                cursor.execute(update_sql)
                self.conn.commit()
            except:
                with open('article_url_error_sql.log', 'a+') as f:
                    f.write(update_sql + '\n')
        else:
            with open('article_url_error_sql.log', 'a+') as f:
                f.write(data['title'] + data['university'] + data['years'] + '\n')

    def InsertIntoArticle(self, title, university, years, url):
        cursor = self.conn.cursor()
        cursor.execute(f"select * from article where title='{title}' and university='{university}' and years='{years}'")
        if len(cursor.fetchall()) == 0:
            cursor.execute(
                f'insert into article (title, university, years, url) values ("{title}","{university}","{years}","{url}")'
            )
            self.conn.commit()

    def GetOneArticle(self):
        cursor = self.conn.cursor()
        id = self.r.get_scroll()
        cursor.execute(
            f"select title,university,years,article_id,id from article where status=0 and id>{id} and university != 'Ph.D.' order by id limit 1")
        if len(cursor.fetchall()) > 0:
            article = cursor._rows[0]
            article_id = article[3]
            id = article[4]
            self.r.set_scroll(id)
            cursor.execute(
                f"update article set status=-2 where id='{id}'")
            self.conn.commit()
            return article

    def UpdateArticleLocalUrl(self, local_url, id):
        update_sql = f"update article set local_url='{local_url}',status=1 where id={id}"
        try:
            cursor = self.conn.cursor()
            cursor.execute(update_sql)
            self.conn.commit()
        except Exception as e:
            with open('error_sql.log', 'a+') as f:
                f.write(update_sql + '\n')

    def UpdateArticleStatus(self, title, university, years):
        cursor = self.conn.cursor()
        update_sql = f"update article set status=-1 where title='{title}' and university='{university}' and years='{years}'"
        cursor.execute(update_sql)
        self.conn.commit()

    def GetKeyWords(self):
        cur = self.conn.cursor()
        cur.execute(f"select * from article where id<145000 order by id desc limit 1000")
        for item in cur.fetchall():
            title = item[1]
            words = jieba.lcut(title)
            for word in words:
                if len(word) > 1:
                    cur.execute(f"select * from search_key where `key` = '{word}'")
                    if len(cur.fetchall()) == 0:
                        print(word)
                        insert_sql = f"INSERT INTO search_key (`key`) VALUES ('{word}')"
                        cur.execute(insert_sql)
                        self.conn.commit()

    def GetOneArticleForPeriodical(self):
        self.reConnect()
        cursor = self.conn.cursor()
        id = self.r.get_p_scroll()
        cursor.execute(
            f"select id,article_id from periodical where id>{id} order by id limit 1")
        if len(cursor.fetchall()) > 0:
            article = cursor._rows[0]
            article_id = article[1]
            scroll_id = article[0]
            self.r.set_p_scroll(scroll_id)
            self.conn.commit()
            return article_id

    def GetOnePeriodical(self):
        self.reConnect()
        cursor = self.conn.cursor()
        id = self.r.get_periodical_scroll()
        self.r.set_periodical_scroll(str(int(id) + 1))
        cursor.execute(
            f"select id,title,author,url,article_id from periodical where id>{id} order by id limit 1")
        if len(cursor.fetchall()) > 0:
            article = cursor._rows[0]
            id = article[0]
            title = article[1]
            author = article[2]
            url = article[3]
            article_id = article[4]
            self.conn.commit()
            return id, title, url, article_id, author

    def GetBatchPeriodical(self, start, batch_size):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(
            f"select id,title,author,url,article_id from periodical where id>{start} order by id limit {batch_size}")
        if len(cursor.fetchall()) > 0:
            redis_data = []
            for item in cursor._rows:
                article = item
                id = article[0]
                title = article[1]
                author = article[2]
                url = article[3]
                article_id = article[4]
                redis_string = f'{id}##{title}##{author}##{url}##{article_id}'
                redis_data.append(redis_string)
            return redis_data

    def GetBatchPeriodicalError(self, start, batch_size):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(
            f"select id,title,author,url,article_id from periodical where id>{start} and status='error' order by id limit {batch_size}")
        if len(cursor.fetchall()) > 0:
            redis_data = []
            for item in cursor._rows:
                article = item
                id = article[0]
                title = article[1]
                author = article[2]
                url = article[3]
                article_id = article[4]
                redis_string = f'{id}##{title}##{author}##{url}##{article_id}'
                redis_data.append(redis_string)
            start = cursor._rows[499][0]
            return start, redis_data

    def VerifyPeriodical(self, title, author):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(
            f"select id from periodical where title='{title}' and author='{author}'")
        if len(cursor.fetchall()) > 0:
            return False
        return True

    def UpdateOnePeriodical(self, id, periodical, imgPath):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(f"update periodical set periodical='{periodical}', local_path='{imgPath}' where `id`={id}")
        self.conn.commit()

    def UpdateOnePeriodicalStatus(self, id, status, text):
        self.reConnect()
        cursor = self.conn.cursor()
        mysql_text = escape_string(text)
        cursor.execute(f"update periodical set `status`='{status}',text='{mysql_text}' where `id`={id}")
        self.conn.commit()

    def InsertIntoPeriodical(self, data):
        insert_data = []
        for item in data:
            item_data = []
            item_data.append(item['title_cn'])
            item_data.append(''.join(item['authors_cn']))
            item_data.append(item['id'])
            item_data.append(item['url'])
            insert_data.append(item_data)
        sql = 'INSERT INTO periodical(title, author, article_id,url) VALUES (%s, %s, %s, %s)'
        cursor = self.conn.cursor()
        try:
            res = cursor.executemany(sql, insert_data)
            print(res)
            self.conn.commit()
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.conn.close()

    def insertIntoOssFile(self, data):
        data = escape_string(data)
        cursor = self.conn.cursor()
        cursor.execute(
            f'insert into oss_file (title) values ("{data}")'
        )
        self.conn.commit()


    def getOssFile(self, id):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(
            f"select id,title from oss_file where id={id}")
        if len(cursor.fetchall()) > 0:
            pdf = cursor._rows[0]
            id = pdf[0]
            title = pdf[1]
            return id, title


    def updateOssFileStatus(self, id, status):
        self.reConnect()
        cursor = self.conn.cursor()
        cursor.execute(f"update oss_file set `status`={status} where `id`={id}")
        self.conn.commit()



#
# if __name__ == '__main__':
#     remote_db = ArticleDB()
#     remote_db.GetKeyWords()
